﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;

namespace TbShop.Web.Helpers
{
    public class ExcelHelper
    {
        /// <summary>
        /// 根据DataTable中的数据生成Excel文件
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static void GenerateFormDataTable(string excelPath, DataTable dataTable)
        {
            if (File.Exists(excelPath))
                File.Delete(excelPath);

            int rowNumber = dataTable.Rows.Count;
            int columnNumber = dataTable.Columns.Count;

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = false;
            var colIndex = 0;
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }
            //填充数据
            for (int c = 0; c < rowNumber; c++)
            {
                for (int j = 0; j < columnNumber; j++)
                {
                    excel.Cells[c + 2, j + 1] = dataTable.Rows[c][j];
                }
            }
            try
            {
                excel.ActiveWorkbook.SaveAs(excelPath);
            }
            catch { }
            excel.Quit();
        }

        public static DataTable GetDataTableFormExcel(string excelPath)
        {
            if (File.Exists(excelPath))
            {
                try
                {
                    DataSet ds = new DataSet();
                    string strExtension = System.IO.Path.GetExtension(excelPath);
                    OleDbConnection objConn = null;
                    switch (strExtension)
                    {
                        case ".xls":
                            objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"");
                            break;
                        case ".xlsx":
                            objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"");
                            break;
                        default:
                            objConn = null;
                            break;
                    }
                    if (objConn == null)
                        return null;
                    objConn.Open();
                    System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    string tableName = schemaTable.Rows[0][2].ToString().Trim();
                    string strSql = "select * from [" + tableName + "]";
                    OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
                    OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
                    myData.Fill(ds, tableName);
                    objConn.Close();
                    return ds.Tables[tableName];
                }
                catch
                {
                    return null;
                }
            }
            return null;
        }

        public static DataSet GetDataSetFormExcel(string excelPath)
        {
            if (File.Exists(excelPath))
            {
                try
                {
                    DataSet ds = new DataSet();
                    string strExtension = System.IO.Path.GetExtension(excelPath);
                    OleDbConnection objConn = null;
                    switch (strExtension)
                    {
                        case ".xls":
                            //objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"");
                            objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';");
                            break;
                        case ".xlsx":
                            objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"");
                            break;
                        default:
                            objConn = null;
                            break;
                    }
                    if (objConn == null)
                        return null;
                    objConn.Open();
                    System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    for (var i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        string tableName = schemaTable.Rows[i][2].ToString().Trim();
                        string strSql = "select * from [" + tableName + "]";
                        OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
                        OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
                        myData.Fill(ds, tableName);
                    }
                    objConn.Close();
                    return ds;
                }
                catch
                {
                    return null;
                }
            }
            return null;
        }
    }
}